So a while back I posted about query layers and how they would eventually replace spatial views. I’ve found query layers to be immensely useful where I’ve needed the flexibility of direct SQL to create a new spatial layer.
Recently a customer asked me to help migrate their SDE binary data to SQL Server Geometry and they had a bunch of “real” (i.e. old school) spatial views. We both spent a bit of time looking how we could best replace the old school spatial views without installing the SDE command line tools (i.e. sdetable etc).
Query layers is one option but it requires you to create the query layer each time.
ArcGIS 10.1/10.2 have a tool called “Create View” which is meant to create what should replace a old style spatial view. In fact the SDE command line reference document actually states the following under the sdetable -o create_view command;
You can use the Create View context menu item in the ArcGIS Catalog tree, the Create View geoprocessing tool, or a Python script to create a view on a table.
So one would think this is the way to go, however there is something missing.
When you run the tool it does indeed create a view. ArcCatalog even recognises that it is spatially enabled and you can even preview it. The problem however is when you add the view to ArcMap (either drag/drop or Add Data) you are prompted to nominate the unique identifier column (i.e. similar to OBJECTID) using the same dialog as when you create a query layer. Only once you’ve nominated the unique identifier can you add the spatial view to ArcMap and use it as per usual (and it works well).
The main issue I have with this is that there doesn’t appear to be any way to get around this prompt (such as register the view with the Geodatabase). It means that every time you add the view you have to nominate the unique identifier.
So, at least for now, the only way to have a permanent spatial view (in the schema) that doesn’t prompt you for input is to is to (yup, you guessed it!) install and use the SDE command line tools and create an old school spatial view!
Have you looked at performance differences between old-school spatial views and query layers?
I don’t have the numbers in front of me, but I seem to recall some cursory tests I ran after 10.1 came out had query layers not performing all that well. Query layers do offer a lot of flexibility, but performance needs to be there as well.
Good question, I will do some tests.
I have also noticed performance problems with Query Layers, mostly when performing maintenance on the layers in ArcMap.
I have also found it difficult or impossible to script modifications to map documents containing Query Layers, which makes them unsuitable for inclusion in map services.
Publishing/starting/stopping a map service with more than just a few Query Layers can take a very long time, and doing trivial tasks like changing the data source (i.e. changing the database connection from development to production) requires clicking through the GUI, as it can’t be scripted (unlike changing the data source of an SDE-registered layer).
I’ve stopped using them in favor of registration using sdelayer. But with the SDE command-line tools being deprecated after 10.2, I’m not sure what my options are. As of now, arcpy.RegisterWithGeodatabase_management still does not support view registration, so I may be dead in the water.